import redis
from dbutils.pooled_db import PooledDB
from fastapi import Depends, FastAPI, HTTPException, Query, Security, status, Form, Body
from fastapi.responses import JSONResponse
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy.orm import Session
from pydantic import BaseModel, ValidationError, EmailStr
from typing import List, Optional, Tuple
from fastapi.security import (
    OAuth2PasswordBearer,
    OAuth2PasswordRequestForm,
    SecurityScopes,
)
from datetime import datetime, timedelta
from fastapi import Request
from pydantic import BaseModel
from common.callback import *
from common import models, schemas, logger, callback
from common.database import SessionLocal, engine, testdb
from common.database_company import SessionLocal1, engine1
from common.log_to_sql import get_logger
from company import companybase
from user import charge
from user import crud_user
from company import crud_company
from conf.settings import *
from fastapi import FastAPI
from slowapi import Limiter, _rate_limit_exceeded_handler
from slowapi.errors import RateLimitExceeded
from slowapi.util import get_remote_address
from fastapi.encoders import jsonable_encoder
from captcha.image import ImageCaptcha
from random import randint
from common.mysql import MySQLCommand


def get_db():
    while True:
        try:
            db = SessionLocal()
            return db
        except:
            tlogger.info('尝试重连mysql')
        finally:
            db.close()

def get_redis_db():
    while True:
        try:
            redis_db = redis.StrictRedis(host=REDIS_HOST, port=REDIS_PORT, password=REDIS_PW,
                                         decode_responses=True, db=14, health_check_interval=60, retry_on_timeout=True)
            redis_db.ping()
        except Exception as e:
            print('redis连接失败,正在尝试重连')
            continue
        else:
            return redis_db


class UnicornException(Exception):
    def __init__(self, name: str):
        self.name = name


log_sql = get_logger()
log = logger.tlog()
tlogger = log.define()
models.Base.metadata.create_all(bind=engine)
oauth2_scheme = OAuth2PasswordBearer(
    tokenUrl="/token",
    scopes={"me": "Read information about the current user.", "items": "Read items."},
)

limiter = Limiter(key_func=get_remote_address)
app = FastAPI(title='Open API系统', description='此api由Paitse Times提供服务')
app.state.limiter = limiter
app.add_exception_handler(RateLimitExceeded, _rate_limit_exceeded_handler)


@app.exception_handler(UnicornException)
async def unicorn_exception_handler(request: Request, exc: UnicornException):
    return JSONResponse(
        status_code=200,
        content={'code': 400, "message": f"{exc.name}", "data": "", },
    )


def check_key(key: str, db=get_db()):
    key_user = db.query(models.ApiKeyList).filter(
        models.ApiKeyList.apply_key == key).first()
    # 判断key是否已申请
    if not key_user:
        return 1
    # 判断key是否被注销
    if not key_user.is_active:
        return 2


# print(check_key('db724aa6b02211ecaf4400e2693d5511'))
"""
计费模块
"""


#########################################################################################################################################################
# 计费数据格式化
#########################################################################################################################################################
# 调用计数
# @app.post('/tools/bill_result')
# async def count_result(aid: str = Form(...), username: str = Form(...), Date=datetime.now().strftime('%Y-%m-%d %H:%M:%S'), result: int = Form(...),
#                        db: Session = Depends(get_db)):
def bill_count(api: str, username: str, result: int, db=get_db()):
    '''
    计费模块
    @param api: 查看到是哪个api
    @param username: 用户是谁
    @param result: 查询了多少条结果
    @param db:
    @return:
    '''
    db_user = db.query(models.ApiKeyList).filter(models.ApiKeyList.path == api,
                                                 models.ApiKeyList.username == username).first()
    residue_num = db_user.residue_degree
    # 判断余额是否充足
    if residue_num < result:
        # tlogger.info(f'{residue_num}<{result}')
        return False
    # 如果剩余次数足够
    user_uuid = db.query(models.Users.uuid).filter(models.Users.username == username).first()[0]
    # tlogger.info(user_uuid)
    data = schemas.Billing(user_uuid=user_uuid, username=username, result=result, api=api, aid=db_user.apply_id)
    db_user.residue_degree = residue_num - result
    db.commit()
    db.refresh(db_user)
    # 将计费记录保存到数据库
    charge.add_charge(db=db, bill=data)
    return True
    # return resp_200(data='操作成功')


@app.post('/company/base_info')
async def base_info(key: str = Form(...), pars: str = Form(...), db: Session = Depends(get_db)):
    # 判断key是否存在且被激活，1：key无效，2：key申请过但已被注销
    check = check_key(key)
    if check == 1:
        return resp_400(data='key is valid')
    elif check == 2:
        return resp_400(data='Key has been cancelled')
    bid = eval(pars)['bid']
    company_name = eval(pars)['company_name']
    country = eval(pars)['country']
    company_info = db.query(models.Company).filter(models.Company.bid == bid,
                                                   models.Company.company_name == company_name,
                                                   models.Company.country == country).first()
    result = jsonable_encoder(company_info)
    return resp_200(data=result)


@app.post('/product/query_many')
async def get_company_info_by_product(key: str = Form(...), pars: str = Form(...), db: Session = Depends(get_db)):
    check, is_active = db.query(models.ApiKeyList, models.ApiKeyList.is_active).filter(
        models.ApiKeyList.apply_key == key).first()
    # 判断key是否已激活
    check = check_key(key)
    if check == 1:
        return resp_400(data='key is valid')
    elif check == 2:
        return resp_400(data='Key has been cancelled')


@app.post('/company')
async def get_many_company_by_keyword(request:Request, key: str = Form(...), page: int = Query(1), limit: int = Query(10),
                                      keyword: str = Query(...)):
    '''
    根据关键字批量获取公司信息，可以给客户两个选项每次五个或者每次十个
    @param key: 钥匙
    @param page: 第几页的数据
    @param limit: 需要多少数据, 默认返回10条
    @param keyword: 关键字
    @return: 公司数据
    '''
    tlogger.info(request.client.host)
    check = check_key(key)
    if check == 1:
        return resp_400(data='key is valid')
    elif check == 2:
        return resp_400(data='Key has been cancelled')
    # 初始化mysql，获取cursor
    cur = MySQLCommand(DBConfig['userdb'])
    cur.connectMysql()
    # 根据条件查询数据库
    table_name = 'company_info'
    sql_count = f"SELECT COUNT(*) FROM {table_name} WHERE company_name LIKE '%{keyword}%'"
    sql = f"SELECT bid,company_name,city FROM {table_name} WHERE company_name LIKE '%{keyword}%' LIMIT {(page - 1) * limit},{limit}"
    result = cur.queryMysql(sql)
    # 统计返回总数
    result_count = cur.queryMysql(sql_count)  # 需要判断查询数是否为空，为空就返回空值，不为空，扣费返回即可
    # tlogger.info(result_count)
    # 根据key获取用户信息
    sql_user = f"SELECT username,path FROM user_api_key WHERE apply_key='{key}'"
    # tlogger.info(sql_user)
    username, api = cur.queryOne(sql_user)
    # 计费，减少key的使用次数
    check_bill = bill_count(api=api, username=username, result=limit)
    if not check_bill:
        return resp_200(data='余额不足，请及时充值')
    return resp_200(data={
        'total': result_count,
        'data': result
    })


@app.post('/company/query_pars')
async def get_company_by_pars(key: str = Form(...), fields: str  = Form("*"),
                              filters: str = Form(None)):
    '''
    根据查询参数获取公司信息
    @param key: 钥匙
    @param field: 查询字段
    @param filters: 筛选条件
    @return: 批量公司信息
    '''

    _table_name = 'company_info'
    check = check_key(key)
    if check == 1:
        return resp_400(data='key is valid')
    elif check == 2:
        return resp_400(data='Key has been cancelled')
    sql = f"SELECT {fields} FROM {_table_name} WHERE is_active=1 LIMIT 10"
    tlogger.info(sql)
    cur = MySQLCommand(DBConfig['userdb'])
    cur.connectMysql()
    result = cur.queryMysql(sql)
    # tlogger.info(result)
    return resp_200(data=str(result))
